﻿using System.Data;
using System.Data.SqlClient;

namespace WebAPI存储过程多接口聚合.SQLHelper
{
    public class SQLServerHelper
    {
        private readonly IConfiguration _configuration;
        public SQLServerHelper(IConfiguration connectionString)
        {
            _configuration = connectionString;
        }

        public void ExecuteNonQuery(string storedProcedureName, string json,string dataKey)
        {
            string connString = _configuration.GetConnectionString(dataKey);
            SqlParameter parameters = new SqlParameter("@UserData", SqlDbType.NVarChar)
            {
                Value = json
            };
            using (SqlConnection connection = new SqlConnection(connString))
            {
                using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    if (parameters != null)
                    {
                        command.Parameters.Add(parameters);
                    }

                    connection.Open();

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

        public DataTable ExecuteQuery(string storedProcedureName,string json,string dataKey)
        {
            string connString = _configuration.GetConnectionString(dataKey);
            DataTable dataTable = new DataTable();
            SqlParameter parameters = new SqlParameter("@UserData", SqlDbType.NVarChar)
            {
                Value = json
            };
            using (SqlConnection connection = new SqlConnection(connString))
            {
                using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    if (parameters != null)
                    {
                        command.Parameters.Add(parameters);
                    }

                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        try
                        {
                            adapter.Fill(dataTable);
                            return dataTable;
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                      
                    }
                }
            }
        }


        //多个参数
        //public void ExecuteNonQuery(string storedProcedureName, params SqlParameter[] parameters)
        //{
        //    using (SqlConnection connection = new SqlConnection(_connectionString))
        //    {
        //        using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
        //        {
        //            command.CommandType = CommandType.StoredProcedure;
        //            if (parameters != null)
        //            {
        //                command.Parameters.AddRange(parameters);
        //            }

        //            connection.Open();
        //            command.ExecuteNonQuery();
        //        }
        //    }
        //}

        //public DataTable ExecuteQuery(string storedProcedureName, params SqlParameter[] parameters)
        //{
        //    using (SqlConnection connection = new SqlConnection(_connectionString))
        //    {
        //        using (SqlCommand command = new SqlCommand(storedProcedureName, connection))
        //        {
        //            command.CommandType = CommandType.StoredProcedure;
        //            if (parameters != null)
        //            {
        //                command.Parameters.AddRange(parameters);
        //            }

        //            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        //            {
        //                DataTable dataTable = new DataTable();
        //                adapter.Fill(dataTable);
        //                return dataTable;
        //            }
        //        }
        //    }
        //}

        //public void InsertUser(string username, string password)
        //{
        //    SqlParameter[] parameters = new SqlParameter[]
        //    {
        //    new SqlParameter("@Username", SqlDbType.NVarChar) { Value = username },
        //    new SqlParameter("@Password", SqlDbType.NVarChar) { Value = password }
        //    };

        //    ExecuteNonQuery("InsertUser", parameters);
        //}

        //public void DeleteUser(int id)
        //{
        //    SqlParameter[] parameters = new SqlParameter[]
        //    {
        //    new SqlParameter("@Id", SqlDbType.Int) { Value = id }
        //    };

        //    ExecuteNonQuery("DeleteUser", parameters);
        //}

        //public void UpdateUser(int id, string username, string password)
        //{
        //    SqlParameter[] parameters = new SqlParameter[]
        //    {
        //    new SqlParameter("@Id", SqlDbType.Int) { Value = id },
        //    new SqlParameter("@Username", SqlDbType.NVarChar) { Value = username },
        //    new SqlParameter("@Password", SqlDbType.NVarChar) { Value = password }
        //    };

        //    ExecuteNonQuery("UpdateUser", parameters);
        //}

        //public DataTable GetUserById(int id)
        //{
        //    SqlParameter[] parameters = new SqlParameter[]
        //    {
        //    new SqlParameter("@Id", SqlDbType.Int) { Value = id }
        //    };

        //    return ExecuteQuery("GetUserById", parameters);
        //}

        //public DataTable GetAllUsers()
        //{
        //    return ExecuteQuery("GetAllUsers");
        //}
    }

}
